﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
namespace Excle
{
    public class DBHelper
    {
        // 连接对象
        static string cnnstr = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
        public static List<T> GetList<T>(string sql)
        {
            DataTable dt = GetDataTable(sql);
            List<T> items = DataTable2List<T>(dt).ToList();
            return items;
        }

        /// <summary>
        /// 返回最后一次插入数据的id（数据库：sql server） 
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static int ExecuteScalar(string sql)
        {
            int i = 0;
            try
            {
                using (SqlConnection Cnn = new SqlConnection(cnnstr))
                {
                    Cnn.Open();
                    SqlCommand cmd = new SqlCommand(sql, Cnn);
                    i = Convert.ToInt32(cmd.ExecuteScalar());
                    Cnn.Close();
                }
            }
            catch (Exception ex)
            {

            }
            return i;
        }

        public static DataTable GetDataTable(string sql)
        {
            DataTable dt = new DataTable();
            try
            {
                using (SqlConnection Cnn = new SqlConnection(cnnstr))
                {
                    Cnn.Open();
                    SqlCommand command = new SqlCommand(sql, Cnn);
                    SqlDataAdapter sda = new SqlDataAdapter(command);
                    sda.Fill(dt);
                    sda.Dispose();
                    Cnn.Close();
                }

            }
            catch (Exception ex)
            {
            }

            return dt;
        }



        /// <summary>
        /// 定义操作(增删改)数据库的方法(重载)
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>

        public static int ExecuteCommand(string sql)
        {
            int i = 0;
            try
            {
                using (SqlConnection Cnn = new SqlConnection(cnnstr))
                {
                    Cnn.Open();
                    SqlCommand cmm = new SqlCommand(sql, Cnn);
                    i = cmm.ExecuteNonQuery();
                    Cnn.Close();
                }
            }
            catch
            {

            }
            return i;
        }

        public static int ExecuteNonQuery(string sql)
        {
            try
            {
                using (SqlConnection Cnn = new SqlConnection(cnnstr))
                {
                    Cnn.Open();
                    SqlCommand cmm = new SqlCommand(sql, Cnn);
                    int sign = cmm.ExecuteNonQuery();
                    Cnn.Close();
                    return sign;
                }
            }
            catch (Exception)
            {

                throw;
            }

        }

        public static List<T> GetList<T>(string sql, SqlParameter[] spart)
        {
            DataTable dt = GetDataTable(sql, spart);
            List<T> items = DataTable2List<T>(dt).ToList();
            return items;
        }
        public static DataTable GetDataTable(string sql, SqlParameter[] spart)
        {
            try
            {
                using (SqlConnection Cnn = new SqlConnection(cnnstr))
                {
                    Cnn.Open();
                    SqlCommand comm = new SqlCommand(sql, Cnn);
                    comm.CommandType = CommandType.StoredProcedure;
                    if (spart != null)
                    {
                        comm.Parameters.AddRange(spart);
                    }
                    SqlDataAdapter sda = new SqlDataAdapter(comm);
                    DataTable dt = new DataTable();
                    sda.Fill(dt);
                    Cnn.Close();
                    return dt;
                }
            }
            catch (Exception)
            {
               
                throw;
            }
        }
        /// <summary>
        /// DataSet装换为泛型集合     
        /// </summary>     
        /// <typeparam name="T"></typeparam>    
        /// <param name="p_DataSet">DataSet</param>    
        /// <param name="p_TableIndex">待转换数据表索引</param>    
        /// <returns></returns>  

        private static IList<T> DataTable2List<T>(DataTable p_Data)
        {
            // 反射技术
            // 返回值初始化       
            IList<T> result = new List<T>();
            for (int j = 0; j < p_Data.Rows.Count; j++)
            {
                T _t = (T)Activator.CreateInstance(typeof(T));
                PropertyInfo[] propertys = _t.GetType().GetProperties();
                foreach (PropertyInfo pi in propertys)
                {
                    for (int i = 0; i < p_Data.Columns.Count; i++)
                    {
                        // 属性与字段名称一致的进行赋值       
                        if (pi.Name.Equals(p_Data.Columns[i].ColumnName))
                        {
                            // 数据库NULL值单独处理       
                            if (p_Data.Rows[j][i] != DBNull.Value)
                                pi.SetValue(_t, p_Data.Rows[j][i], null);
                            else
                                pi.SetValue(_t, null, null);
                            break;
                        }
                    }
                }
                result.Add(_t);
            }
            return result;
        }
    }
}
